Traitements des données Gestion des mises en forme conditionnelles

1.      Appliquer une mise en forme conditionnelle

Mise en forme uniquement de valeurs qui correspondent à une condition

Sélectionner les cellules concernées

Dans l’onglet Accueil, cliquer sur Mise en forme conditionnelle

Choisir l’option Règles de mise en surbrillance des cellules

Sélectionner le type de condition dans la liste

Saisir la valeur ou la cellule contenant la comparaison

o      Sélectionner une mise en forme dans la liste avec

o      Cliquer sur le bouton

ou

o      Sélectionner l’option Format personnalisé à la fin de la liste avec

o      Spécifier la mise forme dans la boîte de dialogue Format de cellule

o      Cliquer deux fois sur le bouton

Mise en forme des valeurs les plus ou moins élevées

Sélectionner les cellules concernées

Dans l’onglet Accueil, cliquer sur Mise en forme conditionnelle

Choisir l’option Règles des valeurs plus/moins élevées

Sélectionner le type de valeur dans la liste

Indiquer le nombre de valeurs et le format à appliquer

Cliquer sur le bouton

Mise en forme de toutes les cellules selon leur valeur

Sélectionner les cellules concernées

Dans l’onglet Accueil, cliquer sur Mise en forme conditionnelle

Sélectionner l’une des options :

o      Barre de données

o      Nuances de couleurs

o      Jeux d’icône

Sélectionner une proposition de mise en forme selon l’option choisie

2.      Gestion des mises en forme conditionnelles

Modifier une mise en forme conditionnelle

Sélectionner les cellules concernées

Dans l’onglet Accueil, cliquer sur Mise en forme conditionnelle

Choisir l’option Gérer les règles

Cliquer sur la description de la règle à modifier

Cliquer sur le bouton Modifier la règle

Procéder aux modifications souhaitées (mise en forme, condition)

Cliquer sur le bouton

Combiner des mises en forme conditionnelles

Sélectionner les cellules concernées

Dans l’onglet Accueil, cliquer sur Mise en forme conditionnelle

Choisir l’option Gérer les règles

Cliquer sur Nouvelle règle

Sélectionner une option sur la liste Style de mise en forme

Spécifier les caractéristiques de la mise en forme

Cliquer sur à deux reprises

Supprimer une mise en forme conditionnelle

Sélectionner les cellules concernées

Dans l’onglet Accueil, cliquer sur Mise en forme conditionnelle

Choisir l’option Gérer les règles

Cliquer sur la description de la règle à supprimer

Cliquer sur le bouton Supprimer la règle

Cliquer sur le bouton

NB : Pour supprimer toutes les règles de la sélection ou d’une feuille de calcul, cliquer sur l’option Effacer les règles dans la liste du bouton Mise en forme conditionnelle, puis sélectionner l’option souhaitée.

Créer une nouvelle mise en forme conditionnelle

Sélectionner les cellules concernées

Dans l’onglet Accueil, cliquer sur Mise en forme conditionnelle

Sélectionner l’une des options :

o      Barre de données

o      Nuances de couleurs

o      Jeux d’icône

Sélectionner ensuite Autres règles

Sélectionner un type de règle

Définir la mise en forme et les conditions

Cliquer sur le bouton

3.      Mise en forme d’un tableau

Appliquer un style pour convertir une plage de cellules en tableau

Sélectionner la plage de cellules à convertir

Cliquer sur l’onglet Accueil

Cliquer sur l’outil Mettre sous forme de tableau du groupe Style

Sélectionner le style à appliquer

Activer l’option Mon tableau comporte des en-têtes si nécessaire (à défaut Excel crée les en-têtes)

Appliquer un style de tableau à un tableau existant

Cliquer dans le tableau

Cliquer sur l’onglet Création

Cliquer sur l’outil Styles rapides du groupe Styles de tableau

Sélectionner le style à appliquer

Personnaliser la présentation appliquée par le style du tableau

Cliquer dans le tableau

Cliquer sur l’onglet Création

Pour masquer la première ligne :

o      Désactivez l’option Ligne d’en-tête dans le groupe Options de style de tableau

Pour appliquer une mise en forme spécifique à la première/dernière colonne :

o      Activer l’option A la première colonne ou A la dernière colonne

Pour activer/désactiver l’alternance des couleurs :

o      Activer ou désactiver les options Lignes à bandes Colonnes à bandes

NB : Toutes ces modifications s’appliquent aux différents styles rapides choisis ultérieurement.

4.      Nom pour une plage de cellule

Nommer une plage de cellule : méthode 1

Sélectionner la plage de cellules à nommer

Utiliser l’option Nommer une plage du menu contextuel

Saisir le nom souhaité sans espace ni trait d’union

Cliquer sur le bouton (le nom apparait dans la Zone nom)

Nommer une plage de cellule : méthode 2

Sélectionner la plage de cellules à nommer

Cliquer dans la Zone nom

Saisir le nom souhaité sans espace ni trait d’union

Valider la saisie (le nom apparait dans la Zone nom

Sélectionner une zone nommée existante

Ouvrir la liste de la Zone nom

Sélectionner le nom de la zone souhaitée

Modifier une zone nommée

Cliquer sur l’onglet Formules

Cliquer sur Gestionnaire de noms

Effectuer les modifications de nom ou de références souhaitées

Cliquer sur le bouton

Utiliser une zone nommée dans une formule : méthode 1

Commencer à saisir la formule de calcul

Taper les 1^ers caractères du nom de la zone

Double-cliquer sur le nom dans l’info-bulle

Terminer la formule puis valider

Utiliser une zone nommée dans une formule : méthode 2

Commencer à saisir la formule de calcul

Dans l’onglet Formules, utiliser le bouton Utiliser dans la formule

Terminer la formule puis valider

5.      Critères de validation

Définir un critère de validation

Cliquer sur l’onglet Données

Cliquer sur le bouton Validation des données

Sélectionner une option sur la liste Autoriser

Définir les critères (les options varient selon le type de données choisies dans la liste Autoriser)

Cliquer sur le bouton

Créer une liste déroulante de valeurs valides

Sélectionner les cellules concernées

Cliquer sur l’onglet Données

Cliquer sur le bouton Validation des données

Sélection l’option Liste de la liste Autoriser

o      Sélectionner les cellules contenant les valeurs de la liste

ou

o      Indiquer le nom de la zone contenant les cellules

ou

o      Saisir les valeurs de la liste en les séparant par un point-virgule

Cliquer sur le bouton

Définir un critère sur la longueur du texte

Sélectionner les cellules concernées

Cliquer sur l’onglet Données

Cliquer sur le bouton Validation des données

Sélection l’option Longueur de texte de la liste Autoriser

Compléter le critère

Cliquer sur le bouton

Afficher un message d’aide à la saisie

Sélectionner les cellules concernées

Cliquer sur l’onglet Données

Cliquer sur le bouton Validation des données

Cliquer sur l’onglet Message de saisie

Renseigner les zone Titre et Message de saisie

Cliquer sur le bouton

Afficher un message en cas d’erreur

Sélectionner les cellules concernées

Cliquer sur l’onglet Données

Cliquer sur le bouton Validation des données

Cliquer sur l’onglet Alerte d’erreur

Activer l’option Quand des données non valides sont tapées

Sélectionner le type d’alerte dans la liste Style

Renseigner les zone Titre et Message d’erreur

Cliquer sur le bouton

NB : Les styles Avertissement et Informations n’empêchent pas la saisie. Pour rejeter une saisie, sélectionner l’option Arrêt

Marquer les données incorrectes

Ouvrir la liste du bouton Validation des données

Pour mettre en évidence les données non valides :

o       Sélectionner l’option Entourer les données non valides

Pour masquer le marquage des données non-valides

o      Sélectionner l’option Effacer les cercles de validation

6.      Conversion de données

Saisir une valeur numérique en tant que texte

Saisir une apostrophe avant la valeur numérique

NB : Pour retirer le triangle vert qui signale une erreur potentielle sur la cellule, cliquer sur la liste associé l’indicateur et sélectionner l’option Ignorer l’erreur

Appliquer un format texte sur une valeur numérique

Activer la cellule concernée

Utiliser l’option Format de cellule du menu contextuel

Cliquer sur l’onglet Nombre

Double-cliquer sur l’option Texte de la liste Catégorie

NB : Pour retirer le triangle vert qui signale une erreur potentielle sur la cellule, cliquer sur la liste associé l’indicateur et sélectionner l’option Ignorer l’erreur

Utiliser les fonctions de conversion

Pour convertir un nombre en texte :

o      Utiliser la fonction TEXTE(valeur;format_de_texte)

Pour convertir un texte en nombre :

o      Utiliser la fonction CNUM texte)

7.      Lignes de sous-totaux

Calculer un sous-total

Trier les données de la colonne sur laquelle le regroupement doit être fait

Sélectionner une cellule de la colonne

Utiliser l’option Trier du menu contextuel

Sélectionner l’option Trier de A à Z

Cliquer sur l’onglet Données

Cliquer sur le bouton Sous-total du groupe Plan

Renseigner les zones de la boîte de dialogue selon les sous-totaux souhaités

Cliquer sur le bouton

Annuler les sous-totaux

Activer une cellule du tableau

Cliquer sur l’onglet Données

Cliquer sur le bouton Sous-total du groupe Plan

Cliquer sur le bouton Supprimer tout

Calculer une moyenne

Cliquer sur le bouton Sous-total du groupe Plan

Sélectionner l’option Moyenne dans la liste Utiliser la fonction, puis renseigner les autres zones

Si nécessaire, activer l’option Remplacer les sous-totaux existants

Dénombrer

Cliquer sur le bouton Sous-total du groupe Plan

Sélectionner l’option Nombre dans la liste Utiliser la fonction, puis renseigner les autres zones

Si nécessaire, activer l’option Remplacer les sous-totaux existants

8.      Organisation des données sous forme de plan

Affichage des niveaux d’un plan

Pour afficher/masquer toutes les lignes d’un niveau :

o      cliquer sur le chiffre correspondant

Pour réduire les lignes d’un plan

o      Cliquer sur le signe qui précède les lignes concernées

Pour développer les lignes d’un plan

o      Cliquer sur le signe qui précède les lignes concernées

Générer un plan automatiquement

Activer une cellule du tableau

Cliquer sur l’onglet Données

Cliquer sur la liste du bouton du groupe Plan

Sélectionner l’option Plan automatique

Supprimer un plan

Activer une cellule du tableau

Cliquer sur l’onglet Données

Cliquer sur la liste du bouton du groupe Plan

Sélectionner l’option Effacer le plan

Créer un plan manuellement

Sélectionner les lignes/colonnes qui doivent faire partie d’un même niveau

Cliquer sur l’onglet Données

Cliquer sur le bouton

Pour ajouter une ligne/colonne à un groupe :

Sélectionner la ligne/colonne

Cliquer sur le bouton

Pour fractionner un groupe :

Sélectionner la ligne/colonne où le groupe va être séparé :

Cliquer sur le bouton

Pour annuler un groupe :

Sélectionner les lignes/colonnes du groupe

Cliquer sur le bouton

9.      Zone de critères

Créer une zone de critères

Saisir ou copier les titres des colonnes à filtrer dans des cellules vides

Combiner les critères d’une colonne avec OU

Saisir les critères de filtre sous le titre de la colonne sur plusieurs lignes (une ligne par OU)

Combiner les critères d’une colonne avec ET

Saisir le titre de la colonne

Saisir un critère en-dessous du titre

Saisir à nouveau le titre de la colonne à côté du précédent

Saisir un autre critère en dessous de ce nouveau titre

Recommencer pour chaque ET

Combiner des critères sur des colonnes différentes

Saisir les titres des colonnes à filtrer dans des cellules contigües en ligne

Saisir les critères en OU sous les titres des colonnes

10. Calculs automatiques dans un tableau

Ligne des totaux

Cliquer dans le tableau

Cliquer sur l’onglet Création

Activer l’option Ligne des totaux du groupe Options de style de tableau

Pour remplacer la fonction de calcul :

o      Cliquer dans la liste déroulante de la cellule qui contient le total

o      Sélectionner la fonction

NB : Chaque cellule de la ligne Total contient une liste de fonction qui peut être activée.

Colonne calculée

Ajouter si nécessaire une colonne au tableau

Cliquer dans une cellule de la colonne vide

Préciser la formule à utiliser dans la 1^ère cellule

La formule se recopie automatiquement

11. Filtrage automatique

Activer/désactiver le filtrage automatique

Cliquer sur l’onglet Accueil

Cliquer sur l’outil Trier et filtrer du groupe Edition

Sélectionner l’option Filtrer

Filtrer le tableau

Ouvrir la liste déroulante de la colonne à filtrer

Choisir la valeur sur laquelle la colonne doit être filtrée

Cliquer sur le bouton

Désactiver un filtre, afficher toutes les lignes

Ouvrir la liste de la colonne dont le filtre est à supprimer

Sélectionner l’option Effacer filtre de « … »

Filtrer sur plusieurs valeurs de colonnes différentes

Activer le premier filtre

Activer le second filtre

NB : Excel affiche les lignes qui correspondent au 1^er critère ET au second critère

Désactiver tous les filtres

Cliquer sur l’onglet Accueil

Cliquer sur l’outil Trier et filtrer du groupe Edition

Sélectionner l’option Effacer

Filtrer par la cellule sélectionnée

Sélectionner une cellule contenant la valeur à filtrer

Sélectionner l’option Filtrer du menu contextuel

Sélectionner l’option Filtrer par la valeur de la cellule sélectionnée

Filtrer des valeurs numériques par rapport à la moyenne

Ouvrir la liste déroulante de la colonne à filtrer

Pointer l’option Filtres numériques

Sélectionner l’option Au-dessus de la moyenne ou Au-dessous de la moyenne

Filtrer des dates par rapport à la date d’aujourd’hui

Ouvrir la liste déroulante de la colonne à filtrer

Sélectionner l’option Filtres Chronologiques

Sélectionner l’option souhaitée

Filtrer selon une mise en forme ou une mise en forme conditionnelle

Ouvrir la liste déroulante de la colonne à filtrer

Sélectionner l’option Filtrer par couleur

Sélectionner la couleur souhaitée

12. Filtres personnalisés

Filtrer les valeurs numériques les plus/moins élevées

Ouvrir la liste déroulante de la colonne à filtrer

Pointer l’option Filtres numériques

Sélectionner l’option 10 premiers

Choisir l’option Haut ou Bas pour obtenir les valeurs les plus élevées o les moins élevées

Préciser le nombre de valeurs souhaitées

Cliquer sur le bouton

Filtrer selon un critère personnalisé

Ouvrir la liste déroulante de la colonne à filtrer

Selon le contenu de la colonne sélectionner :

o       Filtres numériques

o       Filtres chronologiques

o       Filtres textuels

Sélectionner l’option Filtre personnalisé

Choisir l’opérateur de comparaison

Saisir ou choisir la valeur

Cliquer sur le bouton

Filtrer une même colonne selon deux critères

Ouvrir la liste déroulante de la colonne à filtrer

Selon le contenu de la colonne sélectionner :

o       Filtres numériques

o       Filtres chronologiques

o       Filtres textuels

Sélectionner l’option Filtre personnalisé

Choisir l’opérateur de comparaison

Saisir ou choisir la valeur

Activé l’une des options ET ou OU

Choisir le 2^ème opérateur de comparaison

Saisir ou choisir la 2^ème valeur

Cliquer sur le bouton

Filtrer selon un intervalle de données

Ouvrir la liste déroulante de la colonne à filtrer

Selon le contenu de la colonne sélectionner :

o       Filtres numériques

o       Filtres chronologiques

o       Filtres textuels

Sélectionner l’option Entre

Préciser la valeur inférieure

Activer l’option ET

Préciser la valeur supérieure

Cliquer sur le bouton

13. Valeurs vides et doublons

Filtrer les valeurs vides

Pour ne pas afficher les valeurs vides :

o      Ouvrir la liste déroulante de la colonne à filtrer

o       Désélectionner l’option (Vides)

Pour afficher uniquement les valeurs vides

o      Ouvrir la liste déroulante de la colonne à filtrer

o       Désélectionner l’option Sélectionner tout

Sélectionner l’option (Vides)

Supprimer les doublons

Sélectionner une cellule du tableau

Cliquer sur l’onglet Données

Cliquer sur l’outil Supprimer les doublons du groupe Outils de données

Sélectionner les colonnes dans lesquelles se trouvent les doublons

Cliquer sur le bouton deux fois

14. Filtre et copie de lignes par zones de critères

Filtrer avec une zone de critères

Sélectionner une cellule du tableau

Cliquer sur l’onglet Données

Cliquer sur l’outil Avancé du groupe Trier et filtrer

Préciser la plage d’application du filtre dans la zone Plages si nécessaire

Préciser la zone de critères

Cliquer sur le bouton

Afficher toute les lignes

Cliquer dans le tableau filtré

Cliquer sur l’onglet Données

Cliquer sur l’outil Effacer du groupe Trier et filtrer

Copier les lignes répondant à un critère

Sélectionner une cellule du tableau

Cliquer sur l’onglet Données

Cliquer sur l’outil Avancé du groupe Trier et filtrer

Préciser la plage d’application du filtre dans la zone Plages si nécessaire

Préciser la zone de critères

Préciser l’endroit où doivent être copiées les données répondant au filtre dans la zone Copier dans

Cliquer sur le bouton

Créer une zone d’extraction

Copier les en-têtes de colonne où les données doivent être extraites

Cliquer sur l’onglet Données

Cliquer sur l’outil Avancé du groupe Trier et filtrer

Préciser la plage d’application du filtre dans la zone Plages si nécessaire

Préciser la zone de critères

Cliquer sur le bouton

15. Statistiques avec zone de critères

Compter le nombre de lignes

Activer la cellule d’affichage du résultat

Cliquer sur l’outil Insérer une fonction

Sélectionner la catégorie Base de données

Sélectionner la fonction BDNB

Préciser les références des cellules de la base de données dans la zone Base_de_données

Saisir le numéro de la colonne qui sert de base de calcul dans la zone Champ

Préciser la zone de critère utilisée dans la zone Critères

Cliquer sur le bouton

Additionner les valeurs d’une colonne

Activer la cellule d’affichage du résultat

Cliquer sur l’outil Insérer une fonction

Sélectionner la catégorie Base de données

Sélectionner la fonction BDSOMME

Préciser les références des cellules de la base de données dans la zone Base_de_données

Saisir le numéro de la colonne qui sert de base de calcul dans la zone Champ

Préciser la zone de critère utilisée dans la zone Critères

Cliquer sur le bouton

Calculer la moyenne des valeurs d’une colonne

Activer la cellule d’affichage du résultat

Cliquer sur l’outil Insérer une fonction

Sélectionner la catégorie Base de données

Sélectionner la fonction BDMOYENNE

Préciser les références des cellules de la base de données dans la zone Base_de_données

Saisir le numéro de la colonne qui sert de base de calcul dans la zone Champ

Préciser la zone de critère utilisée dans la zone Critères

Cliquer sur le bouton

16. Table de consultation

La fonction RECHERCHEV

Trier par ordre croissant la colonne de la table de consultation qui contient la valeur qui va être recherchée

Activer la cellule d’affichage du résultat

Cliquer sur l’outil Insérer une fonction

Sélectionner la catégorie Recherche & Matrices

Double-cliquer sur la fonction RECHERCHEV

Préciser les arguments de la fonction

o      1 : La valeur cherchée

o      2 : La table de consultation

o      3 : Le numéro de la colonne contenant la valeur à afficher en résultat qui correspond à la valeur cherchée

o      4 : faux pour la correspondance exacte ou vide ou vrai pour une valeur égale ou inférieure à la valeur de recherche

Cliquer sur le bouton